// Parts Copyright 2008 Newgrove Consultants Limited
// Modified By John Diss 
// From original work:
//
// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;

namespace MsSqlSpatial
{
    public static class SpatialRefSysTable
    {
        internal static bool CheckSRIDExists(SqlCommand command, int srid)
        {
            if (srid == -1)
                return true;
            return DatabaseHelper.CheckQueryExists(command,
                                                   @"SELECT [SRID] FROM [ST].[SPATIAL_REF_SYS] WHERE [SRID] = @p0"
                                                   , srid
                );
        }

        internal static bool IsValidSRID(int srid)
        {
            if (srid == -1)
                return true;
            using (SqlConnection connection = DatabaseHelper.ContextConnection)
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                    return CheckSRIDExists(command, srid);
            }
        }

        internal static string GetSRText(int srid)
        {
            if (srid == -1)
                return string.Empty;
            using (SqlConnection connection = DatabaseHelper.ContextConnection)
            {
                connection.Open();
                using (var command = new SqlCommand())
                {
                    command.Connection = connection;
                    DatabaseHelper.BuildCommand(command,
                                                @"SELECT [SRTEXT] FROM [ST].[SPATIAL_REF_SYS] WHERE [SRID] = @p0"
                                                , srid
                        );
                    return (string) command.ExecuteScalar();
                }
            }
        }

        internal static void ExecuteCreate(SqlCommand command)
        {
            if (!DatabaseHelper.CheckMetaTableExists(command, "SPATIAL_REF_SYS"))
            {
                // Create table
                command.CommandText =
                    @"CREATE TABLE [ST].[SPATIAL_REF_SYS](
	[SRID] [int] NOT NULL,
	[AUTH_NAME] [varchar](256) NULL,
	[AUTH_SRID] [int] NULL,
	[SRTEXT] [varchar](2048) NULL,
PRIMARY KEY CLUSTERED ([SRID] ASC)
WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF)
)";
                command.ExecuteNonQuery();

                // Load data
                ExecutePopulate(command);

                // Create trigger
                ExecuteCreateRefreshTransfromCacheTrigger(command);
            }
        }

        internal static void ExecuteBackup(SqlCommand command)
        {
            if (DatabaseHelper.CheckMetaTableExists(command, "SPATIAL_REF_SYS"))
            {
                string backupTableName = "ST_SPATIAL_REF_SYS_BACKUP_" + DateTime.Now.ToString("yyyyMMdd_HHmm");
                command.CommandText = "SELECT * INTO " + backupTableName + " FROM [ST].[SPATIAL_REF_SYS]";
                command.ExecuteNonQuery();
            }
        }

        public static void ExecuteCreateRefreshTransfromCacheTrigger(SqlCommand command)
        {
            if (DatabaseHelper.CheckMetaTableExists(command, "SPATIAL_REF_SYS"))
            {
                Type type = typeof (UserDefinedFunctions);
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"CREATE TRIGGER [{0}].[{1}] 
ON [{0}].[{2}]
WITH EXECUTE AS CALLER 
FOR UPDATE, DELETE 
AS EXTERNAL NAME [{3}].[{4}{5}].[{1}]"
                                                         , "ST"
                                                         , "RefreshTransformCacheTrigger"
                                                         , "SPATIAL_REF_SYS"
                                                         , type.Assembly.GetName().Name
                                                         , (type.Namespace == null ? "" : type.Namespace + ".")
                                                         , type.Name
                                                         ));
                command.ExecuteNonQuery();
            }
        }

        public static void ExecuteDropRefreshTransfromCacheTrigger(SqlCommand command)
        {
            string schemaName = "ST";
            string triggerName = "RefreshTransformCacheTrigger";
            if (DatabaseHelper.CheckTriggerExists(command, schemaName, triggerName))
            {
                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"DROP TRIGGER [{0}].[{1}]"
                                                         , schemaName
                                                         , triggerName
                                                         ));
                command.ExecuteNonQuery();
            }
        }

        // Based on a SharpMap Unit Test (WKTCoordSysParserTests.cs)
        internal static void ExecutePopulate(SqlCommand command)
        {
            DatabaseHelper.BuildCommand(command,
                                        @"INSERT INTO [ST].[SPATIAL_REF_SYS]
VALUES (@SRID,'EPSG',@SRID,@SRTEXT)"
                );
            command.Parameters.Add("@SRID", SqlDbType.Int, -1);
            command.Parameters.Add("@SRTEXT", SqlDbType.VarChar, -1);

            command.Prepare();

            Stream resource = Assembly.GetExecutingAssembly().GetManifestResourceStream("MsSqlSpatial.SRID.csv");
            using (var sr = new StreamReader(resource))
            {
                while (!sr.EndOfStream)
                {
                    string line = sr.ReadLine();
                    int split = line.IndexOf(';');
                    if (split > -1)
                    {
                        string srid = line.Substring(0, split);
                        string wkt = line.Substring(split + 1);
                        command.Parameters["@SRID"].Value = int.Parse(srid);
                        command.Parameters["@SRTEXT"].Value = wkt;
                        command.ExecuteNonQuery();
                    }
                }
            }
            command.Parameters.Clear();
        }
    }
}